from flask import current_app as app

from flask import Blueprint,request,jsonify,make_response

from utils.myfile import myf

from xlrd import open_workbook 

from urllib.parse import quote

# 导入数据库操作类
from views.db import Db

import os

import time

import functools

#后天导出文件
from io import BytesIO

import xlsxwriter

import datetime

import mimetypes


# 计算接口运行时间
def time(func):

    @function.warps(func)

    def wrapper(*args,**kargs):

        startime = time.time()

        func(*args,**kargs)

        endtime= time.time()

        t = endtime - startime

        print("执行了" + str(t) + '秒')



bp_company = Blueprint("company",__name__)


@bp_company.route('/test')
async def test():

    str = "请求ip:"+request.remote_addr+',请求的url'+request.url+'错误信息'+ '\n'

    app.logger.error(str)

    return "hello word"



@bp_company.route('/upload',methods=['POST'])
# 上传excel，并导入数据库
def upload():

    # 获取文件
    file = request.files['file']
    print(file)

    newname = myf.rename(file.filename)

    # 上传文件
    file.save(os.path.join('./static/files/'+newname))

    # 打开文件读取

    name = './static/files/'+newname
    bk = open_workbook(name,encoding_override='utf-8')
    
    sh = bk.sheets()[0]

    # 总行数
    nrows = sh.nrows

    # 列数
    ncols = sh.ncols
    
    for i in range(2,nrows):
        
        row_date = sh.row_values(i)

        for j in range(0,ncols):

            print(row_date[j])
        
    return 'ok'


#后台导出
@bp_company.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


def xtree(data):

    if len(data) <= 0:

        return data

    # 先把data封装成字典
    dict = {}
    list = []

    # 遍历data，for i in data
    for i in data:
        i['label'] = i['name']
        dict[i['id']] = i

    # 判断i有没有父类
    for i in data:

        # 没有父类他为一级，直接加到list
        if not i['pid']:
            list.append(i)
        # 有父类，判断这个父类有没有子类

        else:
            pid = i['pid']
            # 假如没有定义父类的子类列表 dict[i['pid]]['children'] = []
            # 把子类append到子类中
            if 'children' not in dict[pid]:
                dict[pid]['children'] = []
            dict[pid]['children'].append(i)
    return list


# 左侧数据
@bp_company.route('/administrationlist', methods=["GET", "POST"])

def administrationlist():
    
    db = Db()

    sql = "select * from administration"

    res = db.get_all(sql)

    print(res)

    list = xtree(res)

    return jsonify({"code":200,"list":list})


# 获取部门数据
@bp_company.route('/deptlist', methods=["GET", "POST"])

def deptlist():
    
    db = Db()

    sql = "select * from dept"

    res = db.get_all(sql)

    print(res)

    list = xtree(res)

    return jsonify({"code":200,"list":list})


# 获取用户数据
@bp_company.route('/userlist', methods=["GET", "POST"])

def userlist():
    
    db = Db()

    sql = "select * from user"

    res = db.get_all(sql)

    print(res)

    return jsonify({"code":200,"list":res})


# 获取职务数据
@bp_company.route('/dutylist', methods=["GET", "POST"])

def dutylist():
    
    db = Db()

    sql = "select * from duty"

    res = db.get_all(sql)

    print(res)

    return jsonify({"code":200,"list":res})


# 获取岗位数据
@bp_company.route('/postlist', methods=["GET", "POST"])

def postlist():
    
    db = Db()

    sql = "select * from management"

    res = db.get_all(sql)

    print(res)

    return jsonify({"code":200,"list":res})


from utils.es import Es
# 创建索引


@bp_company.route('/estestinsert')
def estestinsert():
    es = Es('testtabel','test-type')

    db = Db()
    try:
        sql = "select * from comany limit 0,10"
        data = db.get_all(sql)
        j=-1
        for i in data:
            j+=1
            es.insert(i)
        return 'true'
    except:
        print('error')
        return 'false'

# 查询数据根据字
@bp_company.route('/es_get')
def es_get():
    es = Es('testtabel','test-type')
    body = {
        #查询一个字的全部数据
           "match":{
            "name":"财务"
        }
        # "multi_match":{
        #     "query":"abc",
        #     "fields":["name", "content"]
        # }
    }
    data = es.find(body)
    address_data = data['hits']['hits']
    print(address_data)
    return 'ok'

    

